#!/use/bin/python
# coding=utf-8
# 早间开机 数据分析-早间开机-完播率
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数

    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#通过通知日志项 key 跟 日期获取设备数量
# isDistinct 是否去重 1 是 0否
# key 通用日志项的key值 如:'tipTaskStart' 早间提醒页面准备启动
# date 日期 如:"2022-08-25"
# msgDict 日用日志项中 msg 的过滤条件: 如 {'result': 1}
def getNumByLogKeyAndDate(isDistinct,key,date,msgDict):
    conn, cur = getDataConnection()
    if isDistinct == 1:
        selectNumSql = f"""
                          select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = '{key}'   
                          """ ;
    else:
        selectNumSql = f"""
                          select count(1) from log_common_service_{yyMMddDate} t where t.`key` = '{key}'   
                          """;

    if len(date) >0:
        selectNumSql = f"""{selectNumSql} and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}' """

    msgWhere = ""
    #对日志表 中msg 数据进行过滤 {'result': 1}
    if msgDict:
        for i, j in msgDict.items():
            #判断value是否是数值
            if isinstance(j, int):
                msgWhere = f""" and JSON_EXTRACT(msg,"$.{i}") ={j}"""
            else:
                msgWhere = f""" and JSON_EXTRACT(msg,"$.{i}") ='{j}'"""

    cur.execute(selectNumSql+msgWhere)
    #print(selectNumSql+msgWhere);
    numResult = cur.fetchone()
    num = 0
    if numResult != None:
        num = numResult[0]
    return num

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    if num is None:
        return 0
    return num

#根据月份 获取本月指定指标的 总和
#tableName 指定的报表
#columnName 指定的指标字段
#date 月份
def getSumNumByTableColumnNameAndDate(tableName,columnName,date):
    conn, cur = getDataConnection()
    selectNumSql = f"""
                     select sum({columnName}) from {tableName} t where 1=1   
                     """;
    if len(date) >0:
        selectNumSql = f"""{selectNumSql} and SUBSTR( t.ref_date, 1,7) = '{date}' and LENGTH(t.ref_date) = 10  """
    cur.execute(selectNumSql)
    #print(selectNumSql)
    numResult = cur.fetchone()
    num = 0
    if numResult!= None:
        num = numResult[0]
    if num is None:
        return 0
    return num

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate

# 记录 数据分析-早间开机-完播率 analysis_zjkj_complete_rate
def insertCompleteRate(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjkj_push_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(f"select count(1) from analysis_zjkj_complete_rate t where 1=1 and  t.ref_date = '{date}'")
        #获取早间开机订阅数
        subscribeNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='zjkj' and t.ref_date =  '{beforeDate}' and t.execute_flag = 0 ")
        if subscribeNum == 0 :
            subscribeNum = selectNumBySql(
            f"select count(1) subNum from life_assistant_devices t where (switches_state1 & (1 << (1 - 1)))<>0 and 	(switches_state1 & (1 << (2 - 1)))<>0	",1)
        # 获取当日早间开机 早上启动播报设备量
        msgDict = {'result': 1}
        showNum = getNumByLogKeyAndDate(1,'openScreen',date,{})
        showRate = getRateByXY(showNum,subscribeNum)
        # 获取所有早上启动播报数量
        showAllNum = selectNumBySql(
            f"select sum(show_num) from analysis_zjkj_complete_rate t where 1=1 and LENGTH(t.ref_date) = 10  ")
        # 获取当日早间开机 早上播报音乐开始设备量
        musicStartNum = getNumByLogKeyAndDate(1,'musicPageStart',date, {})
        # 获取当日早间开机 早上播报音乐结束设备量
        musicEndNum = getNumByLogKeyAndDate(1, 'musicPageFin', date, {})
        # 获取当日早间开机 早上播报天气开始设备量
        weatherStartNum = getNumByLogKeyAndDate(1,'weatherPageStart',date, {})
        # 获取当日早间开机 早上播报天气结束设备量
        weatherEndNum = getNumByLogKeyAndDate(1,'weatherPageFin',date, {})
        # 获取当日早间开机 早上播报音乐开始设备量
        newsStartNum = getNumByLogKeyAndDate(1,'newsPageStart',date, {})
        # 获取当日早间开机 早上播报音乐结束设备量
        newsEndNum = getNumByLogKeyAndDate(1, 'newsPageFin', date, {})

        # 获取音乐 天气 新闻完播率
        musicCompleteRate = getRateByXY(musicEndNum,showNum)
        weatherCompleteRate = getRateByXY(weatherEndNum,showNum)
        newsCompleteRate = getRateByXY(newsEndNum,showNum)

        #服务总体完成次数
        finishNum = getNumByLogKeyAndDate(1, 'tipTaskFinish', date, {})
        finishRate = getRateByXY(finishNum,showNum)
        #获取当日服务自动关机的数量
        msgDict = {'isStandby': 1}
        shutdownNum = getNumByLogKeyAndDate(1, 'tipTaskFinish', date, msgDict)
        shutdownRate = getRateByXY(shutdownNum,showNum)
        #获取当日早间播报完仍留存的数量(未关机)的数量
        msgDict = {'pkg': '待定'}
        retentionNum = getNumByLogKeyAndDate(1, 'tipTaskFinish', date, msgDict)
        #计算留存率
        retentionRate = getRateByXY(retentionNum,showNum)

        #插入或更新
        if countNum == 0:
            logDetail = f"{date}日新增-早间开机开启数量为{showNum},当天订阅数{subscribeNum},开机率为{showRate},累计开机数量为{showAllNum},音乐开启数为{musicStartNum},完播数为{musicEndNum},完播率为{musicCompleteRate}," \
                        f"天气开启数为{weatherStartNum},完播数为{weatherEndNum},完播率为{weatherCompleteRate},新闻开启数为{newsStartNum},完播数为{newsEndNum},完播率为{newsCompleteRate}"\
                        f"\n服务总体完成次数{finishNum},服务总体执行成功率{finishRate},服务自动关机的数量{shutdownNum},服务自动关机率{shutdownRate},早间播报完仍留存的数量(未关机)的数量{retentionNum},留存率{retentionRate}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_zjkj_complete_rate(show_num,show_rate,cumulate_num,music_start_num,music_complete_num,weather_start_num,weather_complete_num,
                news_start_num,news_complete_num,music_complete_rate,weather_complete_rate,news_complete_rate,finish_num,finish_rate,shutdown_num,shutdown_rate,retention_num,retention_rate,ref_date) 
                value({showNum},{showRate},{showAllNum},{musicStartNum},{musicEndNum},{weatherStartNum},{weatherEndNum},{newsStartNum},{newsEndNum},{musicCompleteRate},{weatherCompleteRate},
                {newsCompleteRate},{finishNum},{finishRate},{shutdownNum},{shutdownRate},{retentionNum},{retentionRate},'{date}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-早间开机开启数量为{showNum},当天订阅数{subscribeNum},开机率为{showRate},累计开机数量为{showAllNum},音乐开启数为{musicStartNum},完播数为{musicEndNum},完播率为{musicCompleteRate}," \
                        f"天气开启数为{weatherStartNum},完播数为{weatherEndNum},完播率为{weatherCompleteRate},新闻开启数为{newsStartNum},完播数为{newsEndNum},完播率为{newsCompleteRate}" \
                        f"\n服务总体完成次数{finishNum},服务总体执行成功率{finishRate},服务自动关机的数量{shutdownNum},服务自动关机率{shutdownRate},早间播报完仍留存的数量(未关机)的数量{retentionNum},留存率{retentionRate}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_zjkj_complete_rate set show_num = {showNum},show_rate ={showRate} ,cumulate_num = {showAllNum},music_start_num = {musicStartNum},music_complete_num = {musicEndNum},weather_start_num ={weatherStartNum},weather_complete_num = {weatherEndNum} ,
                news_start_num = {newsStartNum},news_complete_num = {newsEndNum} ,music_complete_rate = {musicCompleteRate},weather_complete_rate = {weatherCompleteRate} ,news_complete_rate = {newsCompleteRate} ,
                finish_num = {finishNum},finish_rate = {finishRate},shutdown_num = {shutdownNum},shutdown_rate = {shutdownRate}, retention_num = {retentionNum},retention_rate = {retentionRate} ,update_time = now()  where ref_date = '{date}' """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 数据分析-早间开机-完播率 analysis_zjkj_complete_rate --本月
def insertCompleteRateMonth(toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjkj_push_rate 表是否已经存在当月 数据
        countNum = selectNumBySql(
            f"select count(1) from analysis_zjkj_complete_rate t where 1=1 and  t.ref_date = '{toMonthDay}'")
        # 获取当月 早间开机 早上启动播报数量
        showNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate','show_num',toMonthDay)
        # 获取所有早上启动播报数量
        showAllNum = selectNumBySql(
            f"select sum(show_num) from analysis_zjkj_complete_rate t where 1=1 and LENGTH(t.ref_date) = 10  ")
        # 获取当日早间开机 早上播报音乐开始设备量
        musicStartNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate','music_start_num',toMonthDay)
        # 获取当日早间开机 早上播报音乐结束设备量
        musicEndNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate','music_complete_num',toMonthDay)
        # 获取当日早间开机 早上播报天气开始设备量
        weatherStartNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate','weather_start_num',toMonthDay)
        # 获取当日早间开机 早上播报天气结束设备量
        weatherEndNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate','weather_complete_num',toMonthDay)
        # 获取当日早间开机 早上播报音乐开始设备量
        newsStartNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate','news_start_num',toMonthDay)
        # 获取当日早间开机 早上播报音乐结束设备量
        newsEndNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate','news_complete_num',toMonthDay)
        # 获取音乐 天气 新闻完播率
        musicCompleteRate = getRateByXY(musicEndNum, showNum)
        weatherCompleteRate = getRateByXY(weatherEndNum, showNum)
        newsCompleteRate = getRateByXY(newsEndNum, showNum)
        # 服务总体完成次数
        finishNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate', 'finish_num', toMonthDay)
        finishRate = getRateByXY(finishNum, showNum)
        # 获取当日服务自动关机的数量
        shutdownNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate', 'shutdown_num', toMonthDay)
        shutdownRate = getRateByXY(shutdownNum, showNum)
        # 获取当日早间播报完仍留存的数量(未关机)的数量
        retentionNum = getSumNumByTableColumnNameAndDate('analysis_zjkj_complete_rate', 'retention_num', toMonthDay)
        # 计算留存率
        retentionRate = getRateByXY(retentionNum, showNum)

        # 插入或更新
        if countNum == 0:
            logDetail = f"{toMonthDay}月新增-早间开机开启数量为{showNum},累计开机数量为{showAllNum},音乐开启数为{musicStartNum},完播数为{musicEndNum},完播率为{musicCompleteRate}," \
                        f"天气开启数为{weatherStartNum},完播数为{weatherEndNum},完播率为{weatherCompleteRate},新闻开启数为{newsStartNum},完播数为{newsEndNum},完播率为{newsCompleteRate}" \
                        f"\n服务总体完成次数{finishNum},服务总体执行成功率{finishRate},服务自动关机的数量{shutdownNum},服务自动关机率{shutdownRate},早间播报完仍留存的数量(未关机)的数量{retentionNum},留存率{retentionRate}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_zjkj_complete_rate(show_num,cumulate_num,music_start_num,music_complete_num,weather_start_num,weather_complete_num,
                        news_start_num,news_complete_num,music_complete_rate,weather_complete_rate,news_complete_rate,finish_num,finish_rate,shutdown_num,shutdown_rate,retention_num,retention_rate,ref_date) 
                        value({showNum},{showAllNum},{musicStartNum},{musicEndNum},{weatherStartNum},{weatherEndNum},{newsStartNum},{newsEndNum},{musicCompleteRate},{weatherCompleteRate},
                        {newsCompleteRate},{finishNum},{finishRate},{shutdownNum},{shutdownRate},{retentionNum},{retentionRate},'{toMonthDay}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{toMonthDay}月更新-早间开机开启数量为{showNum},累计开机数量为{showAllNum},音乐开启数为{musicStartNum},完播数为{musicEndNum},完播率为{musicCompleteRate}," \
                        f"天气开启数为{weatherStartNum},完播数为{weatherEndNum},完播率为{weatherCompleteRate},新闻开启数为{newsStartNum},完播数为{newsEndNum},完播率为{newsCompleteRate}" \
                        f"\n服务总体完成次数{finishNum},服务总体执行成功率{finishRate},服务自动关机的数量{shutdownNum},服务自动关机率{shutdownRate},早间播报完仍留存的数量(未关机)的数量{retentionNum},留存率{retentionRate}"
            print(logDetail)
            # 更新
            updateSql = f"""
                        update analysis_zjkj_complete_rate set show_num = {showNum}  ,cumulate_num = {showAllNum},music_start_num = {musicStartNum},music_complete_num = {musicEndNum},weather_start_num ={weatherStartNum},weather_complete_num = {weatherEndNum} ,
                        news_start_num = {newsStartNum},news_complete_num = {newsEndNum} ,music_complete_rate = {musicCompleteRate},weather_complete_rate = {weatherCompleteRate} ,news_complete_rate = {newsCompleteRate} ,
                        finish_num = {finishNum},finish_num = {finishRate},shutdown_num = {shutdownNum},shutdown_rate = {shutdownRate}, retention_num = {retentionNum},retention_rate = {retentionRate} ,update_time = now()  where ref_date = '{toMonthDay}' """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 30
        jobName = '核心指标-早间开机-完播率'
        tableName = 'analysis_zjkj_complete_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    yyMMddDate = getdateyyMMdd(d)
    beforeDate = getdate(d+1)
    monthdate = getMonthdate(d)
    print (f"{date}日期,执行<数据分析-早间开机-完播率>报表")
    insertCompleteRate(date,beforeDate)
    insertCompleteRateMonth(monthdate)

